﻿ /*== Clean out all old procs ==========================================================*/
	--START CODE--
-- Deleting Old Procs
    declare @procName varchar(500) 
    declare cur cursor
    FOR SELECT name FROM sysobjects WHERE (name LIKE 'TSL_%' OR name LIKE 'MTSL_%' OR name LIKE 'VSLT_%') AND type = 'P'
    OPEN cur 
    fetch next from cur into @procName
    while @@fetch_status = 0
    begin
    exec('drop procedure ' + @procName)
    fetch next from cur into @procName 
    end
    close cur
    deallocate cur
GO

 /*== Clean out all old views ==========================================================*/
	--START CODE--
-- Deleting Old Views
    declare @viewName varchar(500)
    declare cur cursor
    FOR SELECT name FROM sysobjects WHERE (name LIKE 'TSL_%' OR name LIKE 'MTSL_%' OR name LIKE 'VSLT_%') AND type = 'V'
    OPEN cur
    fetch next from cur into @viewName
    while @@fetch_status = 0
    begin
    exec('drop view ' + @viewName)
    fetch next from cur into @viewName 
    end
    close cur
    deallocate cur
GO

 /*== Clean out all old tables ==========================================================*/
	--START CODE--
-- Deleting Old Tables
	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'TSL_LoadTestCoreInfo') AND type in (N'U'))
    BEGIN 
	DROP TABLE TSL_LoadTestCoreInfo
	END
				
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'TSL_ScoreCardMachineCPU') AND type in (N'U'))
    BEGIN 
	DROP TABLE TSL_ScoreCardMachineCPU
	END
				
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'TSL_ScoreCardMachineAvailableMB') AND type in (N'U'))
    BEGIN 
	DROP TABLE TSL_ScoreCardMachineAvailableMB
	END
				
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'TSL_StagingTableForPerfmonImports') AND type in (N'U'))
    BEGIN 
	DROP TABLE TSL_StagingTableForPerfmonImports
	END
				
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'TSL_LoadTestIntervals') AND type in (N'U'))
    BEGIN 
	DROP TABLE TSL_LoadTestIntervals
	END
				
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'TSL_DbChangeLogTypes') AND type in (N'U'))
    BEGIN 
	DROP TABLE TSL_DbChangeLogTypes
	END

	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'VSLT_LoadTestCoreInfo') AND type in (N'U'))
    BEGIN 
	DROP TABLE VSLT_LoadTestCoreInfo
	END
				
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'VSLT_ScoreCardMachineCPU') AND type in (N'U'))
    BEGIN 
	DROP TABLE VSLT_ScoreCardMachineCPU
	END
				
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'VSLT_ScoreCardMachineAvailableMB') AND type in (N'U'))
    BEGIN 
	DROP TABLE VSLT_ScoreCardMachineAvailableMB
	END
				
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'VSLT_StagingTableForPerfmonImports') AND type in (N'U'))
    BEGIN 
	DROP TABLE VSLT_StagingTableForPerfmonImports
	END
				
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'VSLT_LoadTestIntervals') AND type in (N'U'))
    BEGIN 
	DROP TABLE VSLT_LoadTestIntervals
	END
				
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'VSLT_DbChangeLogTypes') AND type in (N'U'))
    BEGIN 
	DROP TABLE VSLT_DbChangeLogTypes
	END
				
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'VSLT_IntervalProfileTypes') AND type in (N'U'))
    BEGIN 
	DROP TABLE VSLT_IntervalProfileTypes
	END
GO


 /*== Restore Original Summary Proc ==========================================================*/
	--START CODE--
-- Restoring Original Prc_UpdateSummaryData3
ALTER PROCEDURE [dbo].[Prc_UpdateSummaryData3] @LoadTestRunId int, @DeleteDetailTables bit
AS
BEGIN
	INSERT INTO LoadTestTestSummaryData 
	    (LoadTestRunId, TestCaseId, TestsRun, Average, Minimum, Maximum, StandardDeviation)
	   SELECT LoadTestRunId, TestCaseId,
	          count(*) as TestsRun,
	          avg(ElapsedTime) as Average, 
	          min(ElapsedTime) as Minimum,
	          max(ElapsedTime) as Maximum,
              ISNULL(STDEVP(ElapsedTime),0) AS StandardDeviation
	    FROM  LoadTestTestDetail 
		WHERE LoadTestRunId=@LoadTestRunId
		      AND InMeasurementInterval = 1
	    GROUP BY LoadTestRunId, TestCaseId

        EXEC Prc_UpdateTestSummary3 @LoadTestRunId


	INSERT INTO LoadTestTransactionSummaryData 
	    (LoadTestRunId, TransactionId, TransactionCount, Average, Minimum, Maximum,AvgTransactionTime, StandardDeviation)
	    SELECT LoadTestRunId, TransactionId,
	           count(*) as TransactionCount,
	           avg(ResponseTime) as Average, 
	           min(ResponseTime) as Minimum,
                   max(ResponseTime) as Maximum,
                   avg(ElapsedTime) as AverageTransactionTime,
                   ISNULL(STDEVP(ResponseTime),0) AS StandardDeviation
	    FROM LoadTestTransactionDetail 
		WHERE  LoadTestRunId=@LoadTestRunId
		       AND InMeasurementInterval = 1
	    GROUP BY LoadTestRunId, TransactionId

        EXEC Prc_UpdateTransactionSummary3 @LoadTestRunId


	INSERT INTO LoadTestPageSummaryData 
	    (LoadTestRunId, PageId, PageCount, Average, Minimum, Maximum, StandardDeviation)
	    SELECT LoadTestRunId, PageId,
	           count(*) as PageCount,
	           avg(ResponseTime) as Average, 
	           min(ResponseTime) as Minimum,
	           max(ResponseTime) as Maximum,
                   ISNULL(STDEVP(ResponseTime),0) AS StandardDeviation
	    FROM   LoadTestPageDetail 
		WHERE  LoadTestRunId=@LoadTestRunId
		       AND InMeasurementInterval = 1
	    GROUP BY LoadTestRunId, PageId

        EXEC Prc_UpdatePageSummary3 @LoadTestRunId


	INSERT INTO LoadTestPageSummaryByNetwork
	    (LoadTestRunId, PageId, NetworkId, PageCount, Average, Minimum, Maximum, Goal, StandardDeviation)
	    SELECT pageDetail.LoadTestRunId, pageDetail.PageId, testDetail.NetworkId,
	           count(*) as PageCount,
	           avg(ResponseTime) as Average, 
	           min(ResponseTime) as Minimum,
	           max(ResponseTime) as Maximum,
	           avg(ResponseTimeGoal) as Goal,
                   ISNULL(STDEVP(ResponseTime),0) AS StandardDeviation
	    FROM   LoadTestPageDetail as pageDetail
	    INNER JOIN LoadTestTestDetail as testDetail
	    ON pageDetail.LoadTestRunId = testDetail.LoadTestRunId
	          AND pageDetail.TestDetailId = testDetail.TestDetailId
	    WHERE pageDetail.LoadTestRunId = @LoadTestRunId
		      AND pageDetail.InMeasurementInterval = 1
	    GROUP BY pageDetail.LoadTestRunId, PageId, testDetail.NetworkId
        
        EXEC Prc_UpdatePageSummaryByNetwork3 @LoadTestRunId

	IF @DeleteDetailTables = 1
	BEGIN
		DELETE from LoadTestTestDetail where LoadTestRunId = @LoadTestRunId
		DELETE from LoadTestTransactionDetail where LoadTestRunId = @LoadTestRunId
		DELETE from LoadTestPageDetail where LoadTestRunId = @LoadTestRunId
	END
END
GO
 /*== Restore Original Deletion Proc ==========================================================*/
	--START CODE--
-- Restoring Original Prc_DeleteLoadTestRun
ALTER PROCEDURE Prc_DeleteLoadTestRun @LoadTestRunId int
AS
BEGIN

    -- First delete the data collector log tables
    EXEC Prc_DeleteDataCollectorLogs @LoadTestRunId

    ----------------------------------------------------------------
    -- TempTable to handle the tables to use for delete.
    -- rownum will insure we retrieve in the right order
    -- so foreign key constraints don't bite us
    DECLARE @Temp TABLE
          (rownum int
           , tableToUse nvarchar(50))

    -- Build out temp table, 
    INSERT INTO @temp VALUES(1, 'LoadTestFileAttachmentChunk')
    INSERT INTO @temp VALUES(2, 'LoadTestFileAttachment')
    INSERT INTO @temp VALUES(3, 'LoadTestDataCollectorLog')
    INSERT INTO @temp VALUES(4, 'LoadTestTestLog')
    INSERT INTO @temp VALUES(5, 'LoadTestBrowsers')
    INSERT INTO @temp VALUES(6, 'LoadTestNetworks')
    INSERT INTO @temp VALUES(7, 'LoadTestDetailMessage')
    INSERT INTO @temp VALUES(8, 'LoadTestTestDetail')
    INSERT INTO @temp VALUES(9, 'LoadTestPageDetail')
    INSERT INTO @temp VALUES(10, 'LoadTestTransactionDetail')
    INSERT INTO @temp VALUES(11, 'LoadTestTestSummaryData')
    INSERT INTO @temp VALUES(12, 'LoadTestTransactionSummaryData')
    INSERT INTO @temp VALUES(13, 'LoadTestPageSummaryData')
    INSERT INTO @temp VALUES(14, 'LoadTestPageSummaryByNetwork')
    INSERT INTO @temp VALUES(15, 'LoadTestCase')
    INSERT INTO @temp VALUES(16, 'LoadTestMessage')
    INSERT INTO @temp VALUES(17, 'LoadTestMessageType')
    INSERT INTO @temp VALUES(18, 'LoadTestThresholdMessage')
    INSERT INTO @temp VALUES(19, 'LoadTestPerformanceCounter')
    INSERT INTO @temp VALUES(20, 'LoadTestPerformanceCounterCategory')
    INSERT INTO @temp VALUES(21, 'LoadTestPerformanceCounterInstance')
    INSERT INTO @temp VALUES(22, 'LoadTestPerformanceCounterSample')
    INSERT INTO @temp VALUES(23, 'LoadTestRunAgent')
    INSERT INTO @temp VALUES(24, 'LoadTestRunInterval')
    INSERT INTO @temp VALUES(25, 'LoadTestScenario')
    INSERT INTO @temp VALUES(26, 'LoadTestSqlTrace')
    INSERT INTO @temp VALUES(27, 'WebLoadTestErrorDetail')
    INSERT INTO @temp VALUES(28, 'WebLoadTestTransaction')
    INSERT INTO @temp VALUES(29, 'WebLoadTestRequestMap')
    INSERT INTO @temp VALUES(30, 'LoadTestSystemUnderTestTag')
    INSERT INTO @temp VALUES(31, 'LoadTestSystemUnderTest')
    INSERT INTO @temp VALUES(32, 'LoadTestRun')

    ----------------------------------------------------------------
    -- Variables to control the behavior of the query
    DECLARE @iEndOfTable int
    DECLARE @iRowsToDelete int
    DECLARE @iRownum int
    DECLARE @tableName nvarchar(50)
    DECLARE @QueryToUse nvarchar(500)

    set @iEndOfTable = 0
    SET @iRowsToDelete = 10000
    SET @iRownum = 1


    WHILE @iRownum < 33
    BEGIN
          SELECT @tableName = tableToUse FROM @TEMP WHERE rownum = @iRownum
          WHILE @iEndOfTable = 0
          BEGIN
                SET @QueryToUse = 'DELETE TOP(' + CAST(@iRowsToDelete AS nvarchar(10)) + ') FROM ' +
                            @tableName + ' WHERE LoadTestRunId = ' +  CAST(@LoadTestRunId AS nvarchar(10))
                EXECUTE sp_executesql @QueryToUse
                IF (@@rowcount < @iRowsToDelete)
                      SET @iEndOfTable = 1
          END
          SET @iRownum = @iRownum + 1
          SET @iEndOfTable = 0
    END
END
GO
